﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class KcDAL:IKcDAL
    {
        public int AddKc(KcInfo kc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, kc.Num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, kc.Ku),
                
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, kc.Pinzhong),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, kc.Guige)
            };
            return (DALHelper.RunCommand(@"insert into dd.KC (num, ku, pinzhong, guige) values (:num, :ku, :pinzhong, :guige)", prams));
        }
        public int UpdateKc(string num, string ku, string pinzhong, string guige)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, pinzhong),
                DALHelper.MakeInParam(":guige", OracleType.VarChar, guige)
            };
            return (DALHelper.RunCommand(@"update dd.KC set  ku = :ku, pinzhong = :pinzhong, guige = :guige where num = :num", prams));
        }
        
        
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.KC where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.KC where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectAll()
        {
            

            return (DALHelper.RunProcReturn(@"select * from dd.KC order by ku ", "SA"));
        
        }

        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "kc" + time.ToString();


            return num;
        }
        public DataSet NumByKu(string ku)
        {
            string bt = "";
            string et = "";
            string sysnow = DateTime.Now.ToString();
            string sysdate = DateTime.Now.ToShortDateString().ToString();
            DateTime jztime = Convert.ToDateTime(sysdate).AddHours(8);
            
            if (Convert.ToDateTime(sysnow).CompareTo(jztime) <= 0)
            {
                bt = jztime.AddHours(-12).ToString();
                et = jztime.ToString();
            }
            else
            {
                bt = jztime.ToString();
                DateTime jstime = jztime.AddHours(12);
                
                et = jstime.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct num from dd.KC where KU = :ku and num not in (select num from dd.kcdt where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss'))", prams, "numrt"));
        }
        public DataSet PByK(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select distinct pinzhong from dd.KC where KU = :ku", prams, "kurt"));
        }
        public DataSet KF()
        {
            return (DALHelper.RunProcReturn(@"select distinct ku from dd.KC", "kfm"));
        }
        public DataSet JBCLPByK(string ku)
        {
            string bt = "";
            string et = "";
            string sday = DateTime.Now.ToShortDateString().ToString();
            bt = sday + "00:00:00";
            et = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select distinct pinzhong from dd.KC where KU = :ku and pinzhong not in (select hb.pinzhong from dd.kfhb hb where hb.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and hb.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and hb.ku = :ku)", prams, "kurt"));
        }
        public DataSet KuNum(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select num from dd.KC where ku = :ku", prams, "KN"));
        }
        public DataSet KuGBP(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select distinct guige from dd.KC where ku = :ku and pinzhong in ('方坯', '板坯')", prams, "KN"));
        }

        
    }
}
